/*
AlliedBridge Content Management System
Copyright (C) 2006 AlliedBridge

This program is free software; you can redistribute it and/or modify it under the
terms of the GNU General Public License as published by the Free Software Foundation;

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program; if not, write to the
Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
*/
package org.volume4.alliedbridge.model.person;

import org.apache.log4j.Logger;

import java.io.*;
import java.sql.*;

import javax.servlet.*;
import javax.servlet.http.*;

public class ActivateMember extends HttpServlet {

    public String DRIVER, URL, USER, PASS;
    public String app_root, _table, messageFailed, messageSuccess, getMemberData, moveMemberData, checkLBData, createLBData,
            moveLBData, removeRedundantData, removeLBData;
    public String mem_number, first_name, last_name, area_represented, area_represented_abr, age, sex, height, waist, chest,
            eye_color, hair_color, shirt_dress_size, shoe_size, modeling_school, cup, best_mc, best_ranking, banner1, banner1_alt,
            banner1_link, banner2, banner2_alt, banner2_link, banner3, banner3_alt, banner3_link, img, img_alt;

    public void init() throws ServletException {
        ServletContext context = getServletContext();
        DRIVER = context.getInitParameter("driver");
        URL = context.getInitParameter("mc_dburl");
        USER = context.getInitParameter("mc_dbuser");
        PASS = context.getInitParameter("mc_dbpass");
    }

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html");
        Connection con = null;
        // NOTE: Find a better place to store these values.
        // Probably a Spring properties file?
        app_root = request.getContextPath();
        messageFailed = "Member could not be found based on member number entered. Please try again.";
        messageSuccess = "Member successfuly activated.";

        try {
            Class.forName(DRIVER);
            con = DriverManager.getConnection(URL, USER, PASS);
            Logger log = Logger.getLogger(MemberAdmin.class);

            //Get member number from request.
            mem_number = request.getParameter("mem_number");

            // NOTE: Find a better place to store these and all other queries.
            // All of this should actually be handled by Hibernate
            // Run query to retrieve all information for this member.
            getMemberData = "SELECT * FROM ab_members_pending" +
                    " WHERE mem_number= '" + mem_number + "'";

            log.info("Getting member data.");
            PreparedStatement stmtGMD = con.prepareStatement(getMemberData);
            ResultSet rs = stmtGMD.executeQuery();
            boolean hasData = false;

            while (rs.next()) {
                hasData = true;
                log.info("Member data found.");

                // Get all information from ResultSet
                mem_number = rs.getString("mem_number");
                first_name = rs.getString("first_name");
                last_name = rs.getString("last_name");
                area_represented = rs.getString("area_represented");
                area_represented_abr = rs.getString("area_represented_abr");
                age = rs.getString("age");
                sex = rs.getString("sex");
                height = rs.getString("height");
                waist = rs.getString("waist");
                chest = rs.getString("chest");
                eye_color = rs.getString("eye_color");
                hair_color = rs.getString("hair_color");
                shirt_dress_size = rs.getString("shirt_dress_size");
                shoe_size = rs.getString("shoe_size");
                modeling_school = rs.getString("modeling_school");
                cup = rs.getString("cup");
                banner1 = rs.getString("banner1");
                banner1_alt = rs.getString("banner1_alt");
                banner1_link = rs.getString("banner1_link");
                banner2 = rs.getString("banner2");
                banner2_alt = rs.getString("banner2_alt");
                banner2_link = rs.getString("banner2_link");
                banner3 = rs.getString("banner3");
                banner3_alt = rs.getString("banner3_alt");
                banner3_link = rs.getString("banner3_link");
                img = rs.getString("img");
                img_alt = rs.getString("img_alt");

                moveMemberData = "INSERT INTO ab_members " +
                        "(mem_number,first_name,last_name,area_represented,area_represented_abr,age,sex,height,waist,chest," +
                        "eye_color,hair_color,shirt_dress_size,shoe_size,modeling_school,cup," +
                        "banner1,banner1_alt,banner1_link,banner2," +
                        "banner2_alt,banner2_link,banner3,banner3_alt,banner3_link,img,img_alt)" +
                        " VALUES ('" + mem_number + "', " +
                        "'" + first_name + "', " +
                        "'" + last_name + "', " +
                        "'" + area_represented + "', " +
                        "'" + area_represented_abr + "', " +
                        "'" + age + "', " +
                        "'" + sex + "', " +
                        "'" + height + "', " +
                        "'" + waist + "', " +
                        "'" + chest + "', " +
                        "'" + eye_color + "', " +
                        "'" + hair_color + "', " +
                        "'" + shirt_dress_size + "', " +
                        "'" + shoe_size + "', " +
                        "'" + modeling_school + "', " +
                        "'" + cup + "', " +
                        "'" + banner1 + "', " +
                        "'" + banner1_alt + "', " +
                        "'" + banner1_link + "', " +
                        "'" + banner2 + "', " +
                        "'" + banner2_alt + "', " +
                        "'" + banner2_link + "', " +
                        "'" + banner3 + "', " +
                        "'" + banner3_alt + "', " +
                        "'" + banner3_link + "', " +
                        "'" + img + "', " +
                        "'" + img_alt + "')";

                log.info("Move the data from ab_members_pending to ab_members.");
                PreparedStatement stmtCopy = con.prepareStatement(moveMemberData);
                int copyResult = stmtCopy.executeUpdate(moveMemberData);

                removeRedundantData = "DELETE FROM ab_members_pending" +
                    " WHERE mem_number= '" + mem_number + "'";

                log.info("Remove the member data from ab_members_pending.");
                PreparedStatement stmtDelete = con.prepareStatement(removeRedundantData);
                int deleteResult = stmtDelete.executeUpdate(removeRedundantData);
            }

            if (!hasData) {
                log.info(messageFailed);
                response.sendRedirect(app_root + "/application/application_messages/process_result.jsp?message=" + messageFailed);
            }

            checkLBData = "SELECT * FROM ab_leader_board_pending " +
                    "WHERE mem_number ='" + mem_number + "'";

            log.info("Checking whether the member has any existing leader board data.");
            PreparedStatement chkLBStmt = con.prepareStatement(checkLBData);
            ResultSet chkLBRs = chkLBStmt.executeQuery();
            ResultSetMetaData rsmd = chkLBRs.getMetaData();
            log.info("QUERY: Checking whether the member has any existing leader board data executed.");

            if (rsmd.getColumnCount() < 1) {

                createLBData = "INSERT INTO ab_leader_board " +
                        "(mem_number, jp) " +
                        "VALUES ('" + mem_number + "', '10')";

                log.info("No existing leader board data found. Create a new entry.");
                PreparedStatement createLBStmt = con.prepareStatement(createLBData);
                int createLBResult = createLBStmt.executeUpdate();

            } else {

                while (chkLBRs.next()) {
                    int mem_number = chkLBRs.getInt("mem_number");
                    int jp = chkLBRs.getInt("jp");
                    int rc = chkLBRs.getInt("rc");
                    int fsmgp = chkLBRs.getInt("fsmgp");
                    int gmc = chkLBRs.getInt("gmc");
                    int lmgp = chkLBRs.getInt("lmgp");
                    int saff = chkLBRs.getInt("saff");
                    int gmgp = chkLBRs.getInt("gmgp");
                    int mmc = chkLBRs.getInt("mmc");
                    int nwmc = chkLBRs.getInt("nwmc");
                    int wcmgp = chkLBRs.getInt("wcmgp");
                    int ncmgp = chkLBRs.getInt("ncmgp");
                    int emc = chkLBRs.getInt("emc");
                    int sapm = chkLBRs.getInt("sapm");
                    int smc = chkLBRs.getInt("smc");
                    int ecc = chkLBRs.getInt("ecc");
                    int sams = chkLBRs.getInt("sams");
                    int kzngp = chkLBRs.getInt("kzngp");
                    int mgp = chkLBRs.getInt("mgp");
                    int gsc = chkLBRs.getInt("gsc");
                    int mmw = chkLBRs.getInt("mmw");
                    int saly = chkLBRs.getInt("saly");
                    int safy = chkLBRs.getInt("safy");
                    int kznc = chkLBRs.getInt("kznc");
                    int ecgp = chkLBRs.getInt("ecgp");
                    int wcc = chkLBRs.getInt("wcc");
                    int fsc = chkLBRs.getInt("fsc");
                    int nwgp = chkLBRs.getInt("nwgp");
                    int nlmc = chkLBRs.getInt("nlmc");
                    int samo = chkLBRs.getInt("samo");
                    int cs = chkLBRs.getInt("cs");
                    int cofc = chkLBRs.getInt("cofc");
                    int ncc = chkLBRs.getInt("ncc");
                    int ggp = chkLBRs.getInt("ggp");
                    int lc = chkLBRs.getInt("lc");
                    int samp = chkLBRs.getInt("samp");
                    int tmc = chkLBRs.getInt("tmc");
                    int gc = chkLBRs.getInt("gc");
                    int yotm = chkLBRs.getInt("yotm");

                    moveLBData = "INSERT INTO ab_leader_board " +
                            "(mem_number,jp,rc,fsmgp,gmc,lmgp,saff,gmgp,mmc,nwmc,wcmgp,ncmgp,emc,sapm,smc,ecc,sams,kzngp,mgp," +
                            "gsc,mmw,saly,safy,kznc,ecgp,wcc,fsc,nwgp,nlmc,samo,cs,cofc,ncc,ggp,lc,samp,tmc,gc,yotm)" +
                            "VALUES ('" + mem_number + "', " +
                            "'" + jp + "', " +
                            "'" + rc + "', " +
                            "'" + fsmgp + "', " +
                            "'" + gmc + "', " +
                            "'" + lmgp + "', " +
                            "'" + saff + "', " +
                            "'" + gmgp + "', " +
                            "'" + mmc + "', " +
                            "'" + nwmc + "', " +
                            "'" + wcmgp + "', " +
                            "'" + ncmgp + "', " +
                            "'" + emc + "', " +
                            "'" + sapm + "', " +
                            "'" + smc + "', " +
                            "'" + ecc + "', " +
                            "'" + sams + "', " +
                            "'" + kzngp + "', " +
                            "'" + mgp + "', " +
                            "'" + gsc + "', " +
                            "'" + mmw + "', " +
                            "'" + saly + "', " +
                            "'" + safy + "', " +
                            "'" + kznc + "', " +
                            "'" + ecgp + "', " +
                            "'" + wcc + "', " +
                            "'" + fsc + "', " +
                            "'" + nwgp + "', " +
                            "'" + nlmc + "', " +
                            "'" + samo + "', " +
                            "'" + cs + "', " +
                            "'" + cofc + "', " +
                            "'" + ncc + "', " +
                            "'" + ggp + "', " +
                            "'" + lc + "', " +
                            "'" + samp + "', " +
                            "'" + tmc + "', " +
                            "'" + gc + "', " +
                            "'" + yotm + "')";

                    log.info("Existing leader board data found. Move data.");
                    PreparedStatement moveLBDataStmt = con.prepareStatement(moveLBData);
                    int moveLBDataResult = moveLBDataStmt.executeUpdate();

                    removeLBData = "DELETE FROM ab_leader_board_pending " +
                            " WHERE mem_number ='" + mem_number + "'";

                    log.info("Remove data from ab_leader_board_pending.");
                    PreparedStatement removeLBStmt = con.prepareStatement(removeLBData);
                    int removeLBResult = removeLBStmt.executeUpdate();
                }
            }

            response.sendRedirect(app_root + "/application/application_messages/process_result.jsp?message=" + messageSuccess);

        } catch (SQLException ex) {
            System.out.println("\nERROR:------ SQLException -----\n");
            while (ex != null) {
                System.out.println("Message: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("ErrorCode :" + ex.getErrorCode());
                ex = ex.getNextException();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (con != null)
                    con.close();
            } catch (SQLException ex) {
                System.out.println("\nERROR:------ SQLException -----\n");
                System.out.println("Message: " + ex.getMessage());
                System.out.println("SQLState: " + ex.getSQLState());
                System.out.println("ErrorCode :" + ex.getErrorCode());
            }
        }
    }

    public void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doGet(request, response);
    }
}